package cn.cqie.frame.service.impl;

import cn.cqie.frame.bo.query.QueryStudentBo;
import cn.cqie.frame.entity.Clazz;
import cn.cqie.frame.entity.Student;
import cn.cqie.frame.entity.SysUser;
import cn.cqie.frame.mapper.StudentMapper;
import cn.cqie.frame.service.ClazzService;
import cn.cqie.frame.service.StudentService;
import cn.cqie.frame.service.SysUserService;
import cn.cqie.frame.utils.AssertUtil;
import cn.cqie.frame.utils.DateUtils;
import cn.cqie.frame.utils.MD5Utils;
import cn.cqie.frame.utils.MyPageHelper;
import cn.cqie.frame.vo.ResultVo;
import cn.cqie.frame.vo.StudentVo;
import cn.hutool.core.bean.BeanUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.Page;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * <p>
 * 学生 服务实现类
 * </p>
 *
 * @author XYHL
 * @since 2021-10-09
 */
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {
    @Autowired
    private  StudentService studentService;
    @Autowired
     StudentMapper studentMapper;
    @Autowired
    private ClazzService clazzService;
    @Autowired
    private SysUserService sysUserService;
    @Override
    /**
     ** 插入学生
     * @author 谢一弘
     * @date 2021/10/11 13:25
     * @param [bo]
     * @retrun java.lang.Boolean
     */
    public Boolean saveStudent(Student bo) {
        LambdaQueryWrapper<Student> lqw = new LambdaQueryWrapper();
        lqw.eq(Student::getCode,bo.getCode());
        Student student = baseMapper.selectOne(lqw);
        AssertUtil.isNull(student, "学号已经存在");
        LocalDateTime sysTime = DateUtils.getLocalDateTime();
        bo.setCreateTime(sysTime);
        int insert = baseMapper.insert(bo);
        bo.setCreateTime(sysTime);
        bo.setUpdateTime(sysTime);
        return insert >0;
    }
    /**
     ** 更新学生信息
     * @author 谢一弘
     * @date 2021/10/11 13:24
     * @param bo
     * @retrun java.lang.Boolean
     */
    @Override
    public Boolean updateStudent(Student bo) {
        boolean b = studentService.updateById(bo);
        return b;
    }
    /**
     ** 删除学生
     * @author 谢一弘
     * @date 2021/10/11 13:24
     * @param studentId
     * @retrun java.lang.Boolean
     */
    @Override
    public Boolean delete(Integer studentId) {
        int i = baseMapper.deleteById(studentId);
        return i>0;
    }
    /**
     ** 查询并分页
     * @author 谢一弘
     * @date 2021/10/11 13:24
     * @param bo
     * @retrun java.util.List<cn.cqie.frame.vo.StudentVo>
     */
    @Override
    public List<StudentVo> list(QueryStudentBo bo) {
        MyPageHelper.startPage(bo);
        List<StudentVo> studentVos = studentMapper.listStudent(bo);
        return entity2Vo(studentVos);
    }
    /**
     ** 转换格式
     * @author 谢一弘
     * @date 2021/10/11 13:24
     * @param studentVos
     * @retrun java.util.List<cn.cqie.frame.vo.StudentVo>
     */
    private List<StudentVo> entity2Vo(List<StudentVo> studentVos) {
        List<StudentVo> voList = studentVos.stream()
                .map(any -> BeanUtil.toBean(any, StudentVo.class))
                .collect(Collectors.toList());
        if (studentVos instanceof Page) {
            Page<StudentVo> page = (Page<StudentVo>) studentVos;
            Page<StudentVo> pageVo = new Page<>();
            BeanUtil.copyProperties(page, pageVo);
            pageVo.addAll(voList);
            voList = pageVo;
        }
        return voList;
    }
 /**
  ** 下载学生导入模板
  * @author 谢一弘
  * @date 2021/10/11 13:23
  * @param
  * @retrun void
  */
    @Override
    public void downloadTemplate() {
        OutputStream out = null;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("sheet1");
            sheet.setDefaultColumnWidth(15);

            HSSFRow row;
            HSSFCell cell;
            // 标题行
            HSSFCellStyle styleColHead = getColumnTopStyle(workbook);
            String[] headers = new String[]{"序号", "姓名", "学号", "性别", "联系电话", "班级代码", "班级名称"};
            row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(styleColHead);
                cell.setCellValue(new HSSFRichTextString(headers[i]));
                System.out.println(cell);
            }
            // 数据行
            HSSFCellStyle styleData = getStyle(workbook);
            int rowCount = 3;
            for (int r = 1; r <= rowCount; r++) {
                row = sheet.createRow(r);
                // 序号
                cell = row.createCell(0);
                cell.setCellStyle(styleData);
                cell.setCellValue(r);
                //姓名
                cell = row.createCell(1);
                cell.setCellStyle(styleData);
                cell.setCellValue("学生"+r);
                //学号
                cell = row.createCell(2);
                cell.setCellStyle(styleData);
                cell.setCellValue("18900030"+r);
                //性别
                cell = row.createCell(3);
                cell.setCellStyle(styleData);
                cell.setCellValue("男");
                //联系电话
                cell = row.createCell(4);
                cell.setCellStyle(styleData);
                cell.setCellValue("1522302531"+r);
                //班级代码
                cell = row.createCell(5);
                cell.setCellStyle(styleData);
                cell.setCellValue("1890003");
                //班级名称
                cell = row.createCell(6);
                cell.setCellStyle(styleData);
                cell.setCellValue("1890003班");
            }
            //输出
            ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = attributes.getResponse();
            response.setContentType("application/octet-stream");
            response.setContentType("application/OCTET-STREAM;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=StoreStockRuntime.xls");
            out = new BufferedOutputStream(response.getOutputStream());
            workbook.write(out);
        } catch (Exception e) {

        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                }
            }
        }
    }
   /**
    ** 批量导入
    * @author 谢一弘
    * @date 2021/10/11 13:23
    * @param file
    * @retrun java.lang.Boolean
    */
    @Override
    public Boolean importExcel(MultipartFile file) throws Exception{
        boolean b = false;
        InputStream is = file.getInputStream();
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        LocalDateTime sysTime = DateUtils.getLocalDateTime();
        int totalRows = sheet.getPhysicalNumberOfRows();
        AssertUtil.isTrue(totalRows>0,"无数据");
        List<Student> all = new ArrayList<>();
        //数据准备
        List<Student> allStudent = studentService.list();
        List<Clazz> allClazz = clazzService.list();
        List<SysUser> allUser = sysUserService.list();
        for (int r = 1;r < totalRows  ;r++){
            Student student = new Student();
            HSSFRow row = sheet.getRow(r);
            String name = getCellStringValue(row,1);
            AssertUtil.isTrue(!StringUtils.isEmpty(name),"第" + r + "行,姓名-无值");
            student.setName(name);
            String code = getCellStringValue(row, 2);
            AssertUtil.isTrue(!StringUtils.isEmpty(code),"第" + r + "行,学号-无值");
            //根据学号判断学生是否存在，避免重复添加
            Student findStudent = allStudent.stream().filter(x -> x.getCode()!=null&&x.getCode().equals(code)).findFirst().orElse(null);
            if(findStudent==null) {
                //性别
                String sexName = getCellStringValue(row, 3);
                System.out.println(sexName);
                AssertUtil.isFalse(StringUtils.isEmpty(sexName) || !(sexName.equals("男") || sexName.equals("女")),"第" + r + "行,性别-无值或值错误");
                student.setSex(sexName.equals("男") ? 0 : 1);
                //联系电话
                String mobile = getCellStringValue(row, 4);
                student.setMobile(mobile);
                student.setCode(code);
                student.setCreateTime(sysTime);
                //班级
                String clazzCode = getCellStringValue(row, 5);
                AssertUtil.isTrue(!StringUtils.isEmpty(clazzCode),"第" + r + "行,班级代码-无值");
                String clazzName = getCellStringValue(row, 6);
                AssertUtil.isTrue(!StringUtils.isEmpty(clazzName),"第" + r + "行,班级名称-无值");
                Clazz findClazz = allClazz.stream().filter(x -> x.getCode().equals(clazzCode) && x.getName().equals(clazzName)).findFirst().orElse(null);
                if (findClazz == null) {
                    //根据班级代码、班级名称自动添加班级
                    //return new RetJson("100", "第" + r + "行,班级-不存在",null);
                    findClazz = new Clazz();
                    findClazz.setCode(clazzCode);
                    findClazz.setName(clazzName);
                    findClazz.setStatus(0);
                    findClazz.setCreateTime(sysTime);
                    clazzService.save(findClazz);
                    //更新已经存在的班级，避免重复添加
                    allClazz.add(findClazz);
                }
                student.setClazzId(findClazz.getId());
                //系统账号
                //学生登录账号=学号
                SysUser findUser = allUser.stream().filter(x -> x.getAccount().equals(code)).findFirst().orElse(null);
                if (findUser == null) {
                    findUser = new SysUser();
                    findUser.setUsername(name);
                    findUser.setAccount(code);
                    //学生登录密码=学号
                    findUser.setPassword(MD5Utils.stringToMD5(code));
                    findUser.setStatus("1");
                    sysUserService.save(findUser);
                    //更新已经存在的学生用户，避免重复添加
                    allUser.add(findUser);
                }
                student.setUserId(findUser.getId());
                all.add(student);
                //避免同一批中有重复的学生
                allStudent.add(student);
            }

        }

        if (all.size() > 0) {
            System.out.println(all);
            b = studentService.saveBatch(all);
        }
        return b;
    }
    /**
     ** 列数据信息单元格样式
     * @author 谢一弘
     * @date 2021/10/11 13:25
     * @param workbook
     * @retrun org.apache.poi.hssf.usermodel.HSSFCellStyle
     */
    private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }
    /**
     ** 列头单元格样式
     * @author 谢一弘
     * @date 2021/10/11 13:25
     * @param workbook
     * @retrun org.apache.poi.hssf.usermodel.HSSFCellStyle
     */
    private HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置单元格底色
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }
    /**
     **  获取单元格的值
     * @author 谢一弘
     * @date 2021/10/11 13:29
     * @param row, colIndex
     * @retrun java.lang.String
     */
    private String getCellStringValue(HSSFRow row, int colIndex) {
        String cellValue = "";
        try {
            HSSFCell cell = row.getCell((short) colIndex);
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    cellValue = cell.getRichStringCellValue().getString().trim();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    cellValue = cell.getNumericCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    //cellValue = cell.getCellFormula() + ""
                    try {
                        cellValue = cell.getStringCellValue();
                    } catch (IllegalStateException e) {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                default:
                    cellValue = "";
            }
        } catch (Exception e) {

        }
        return cellValue;
    }


}
